import traceback
import pyautogui
import os
import time
import pyperclip as clip
import json
import pandas as pd
import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.configUtil import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.mouseMoveUtil import *
import math
import uuid

runMode = '日模式'
# 拼多多-商家后台-商品数据-商品明细(分页)
modeName = '拼多多_商家后台_商品明细'
globalUUID = str(uuid.uuid1())


# 左边点击，直到点击到一级类目，该过程不参与循环
def SPGK(engine, conn):
    # 关闭电霸每天晚上会弹出的一个屏蔽页面
    pyautogui.FAILSAFE = False

    sleep(6)
    pyautogui.scroll(10000)

    # 左边--【物流工具】
    viaCommonUrl('https://mms.pinduoduo.com/orders/order/carriage/list')

    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    # 左边--【商品数据】
    viaCommonUrl('https://mms.pinduoduo.com/sycm/goods_effect')

    jsonFromWebShopName = getSjhtShopName()

    # ★★★★★★默认是商品概况，我们要切换到商品明细
    pyautogui.moveTo(472, 234, 6)
    pyautogui.click()

    # 清除 network
    pyautogui.moveTo(1541, 144, 6)
    pyautogui.click()

    # 日、周、月三种模式
    if runMode == '日模式':
        pyautogui.moveTo(1176, 304, 6)
        pyautogui.click()

    if runMode == '周模式':
        pyautogui.moveTo(1239, 304, 6)
        pyautogui.click()

    if runMode == '月模式':
        pyautogui.moveTo(1306, 304, 6)
        pyautogui.click()

    # 下面是直接切换为50条每页，这样就避免了bug
    # 清除 network
    pyautogui.moveTo(1541, 144, 4)
    pyautogui.click()

    pyautogui.moveTo(1100, 895, 4)
    pyautogui.click()

    sleep(4)

    # 第一次滚到页面底部
    pyautogui.scroll(-10000)

    sleep(2)

    # 点击三角下拉箭头
    pyautogui.moveTo(1022, 941, 4)
    pyautogui.click()

    # 选择50页
    pyautogui.moveTo(1008, 908, 2)
    pyautogui.click()

    # ==============F12的filter过滤框
    pyautogui.moveTo(1571, 167, 6)
    pyautogui.doubleClick()

    # 过滤
    clip.copy('queryGoodsDetailVOList')  # 先复制
    sleep(4)
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    # 选中过滤文件
    pyautogui.moveTo(1540, 325, 6)
    pyautogui.click()

    # 点中json值区域
    pyautogui.moveTo(1812, 497, 6)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # bug fix
    pyautogui.moveTo(1541, 144, 2)

    # 读取剪切板内容
    sleep(2)
    value = clip.paste()
    jsonFromWeb = json.loads(value)
    df = pd.read_json(json.dumps(jsonFromWeb['result']['goodsDetailList']))
    totalRows = jsonFromWeb['result']['totalNum']

    df['statDate'] = datetime.datetime.now()
    df['shopName'] = jsonFromWebShopName['result']['username']
    df['pageNumber'] = 0

    # 拼多多_商家后台_商品明细（第一页保存）
    df['统计起始日'] = ''
    df['统计结束日'] = ''
    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    df['插入时间'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    df['uuid'] = globalUUID
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-30)).strftime(
            "%Y-%m-%d")
        df['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                      datetime.datetime.now().day)
        df['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-7)).strftime(
            "%Y-%m-%d")
        df['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['statDate'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d")
        df['统计日周月'] = df['统计日']
    # 解决可能出现的超时问题bugfix 2020.08.18
    df = df.drop(columns=['imprUsrCnt','imprUsrCntPpr','imprUsrCntPprIsPercent'])
    conn.connection.connection.ping(reconnect=True)
    df.to_sql(name='拼多多_商家后台_商品明细', con=conn, if_exists='append', index=False)

    # 后续点击下一页保存（修改为50条每页了）
    for i in range(1, math.ceil(totalRows / 50)):
        # # 断点续传
        # if i < 18:
        #     continue

        # 清空network
        pyautogui.moveTo(1541, 144, 6)
        pyautogui.click()

        # 低页
        pyautogui.moveTo(1084, 593, 6)
        sleep(2)
        pyautogui.scroll(-20000)

        # 点击下一页
        pyautogui.moveTo(1370, 942, 6)
        pyautogui.click()

        # F12 filter输入框
        pyautogui.moveTo(1524, 168, 6)
        pyautogui.doubleClick()

        # 过滤
        clip.copy('queryGoodsDetailVOList')  # 先复制
        # bug fix
        sleep(4)
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # 选中过滤文件
        pyautogui.moveTo(1540, 325, 2)
        pyautogui.click()

        # 点中json值区域
        pyautogui.moveTo(1812, 497, 4)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # bug fix
        pyautogui.moveTo(1637, 750, 2)

        # 读取剪切板内容
        sleep(2)
        value = clip.paste()
        jsonFromWebLoop = json.loads(value)
        dfLoop = pd.read_json(json.dumps(jsonFromWebLoop['result']['goodsDetailList']))
        dfLoop['statDate'] = datetime.datetime.now()
        dfLoop['shopName'] = jsonFromWebShopName['result']['username']

        dfLoop['pageNumber'] = i + 1
        # 拼多多_商家后台_商品明细（第一页保存）
        dfLoop['统计起始日'] = ''
        dfLoop['统计结束日'] = ''
        dfLoop['运行模式'] = runMode
        dfLoop['统计月'] = ''
        dfLoop['统计周'] = ''
        dfLoop['统计日'] = ''
        dfLoop['统计日周月'] = ''
        dfLoop['插入时间'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        dfLoop['uuid'] = globalUUID
        if runMode == '月模式':
            dfLoop['统计月'] = str(datetime.datetime.now().month - 1) + "月"
            dfLoop['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-30)).strftime(
                "%Y-%m-%d")
            dfLoop['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d")
            dfLoop['统计日周月'] = dfLoop['统计月']
        if runMode == '周模式':
            dfLoop['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                              datetime.datetime.now().day)
            dfLoop['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-7)).strftime(
                "%Y-%m-%d")
            dfLoop['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d")
            dfLoop['统计日周月'] = dfLoop['统计周']
        if runMode == '日模式':
            dfLoop['statDate'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d")
            dfLoop['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
            dfLoop['统计起始日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d")
            dfLoop['统计结束日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
                "%Y-%m-%d")
            dfLoop['统计日周月'] = dfLoop['统计日']
        # 解决可能出现的超时问题bugfix 2020.08.18
        dfLoop = dfLoop.drop(columns=['imprUsrCnt','imprUsrCntPpr','imprUsrCntPprIsPercent'])
        conn.connection.connection.ping(reconnect=True)
        dfLoop.to_sql(name='拼多多_商家后台_商品明细', con=conn, if_exists='append', index=False)

        # 鼠标抖动一下，标志一轮循环完成
        sleep(4)

    resetGuiAtLastStepCloseF12ToTop()

    return


def deleteDuplicationData(engine):
    # 这里有特殊的逻辑，不同账号，可能有相同的类目，同一天的数据，是一模一样的，因此我们需要去除重复数据的
    # =====================================================================================================
    # 拼多多_商家后台_服务数据_评价数据_店铺DSR
    sql = ' delete from 拼多多_商家后台_商品明细                                                              '
    sql += '    where statDate=\'' + datetime.datetime.now().strftime('%Y-%m-%d') + '\' and 运行模式=\'' + runMode + '\'   '

    engine.execute(sql)


def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine('拼多多_商家后台_商品明细', engine, conn, '', runMode)
        deleteDuplicationData(engine)
        SPGK(engine, conn)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
